package Chapter01.H20211215;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;

/**
 * @author oldliu
 * @since 1.0
 */

public class SubjectDao {
    public int update(Subject s){
        return 0;
    }
    //查询全部课程信息
    public List<Subject> findALL(){
        List<Subject>datas=new LinkedList<>();
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=connection.prepareStatement("select * from s_subject order by id desc");
            rs=ps.executeQuery();
            while (rs.next()){
                Subject su=new Subject();
                su.setId(rs.getLong("id"));
                su.setSubjectname(rs.getString("subjectname"));
                su.setClasshour(rs.getInt("classhour"));
                su.setGradeid(rs.getLong("gradeid"));
                datas.add(su);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return datas;
    }
    //根据主键找单个对象的查询方法
    public Subject get(long id){
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=connection.prepareStatement("select * from s_subject where id=?");
            ps.setLong(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                Subject su=new Subject();
                su.setId(rs.getLong("id"));
                su.setSubjectname(rs.getString("subjectname"));
                su.setClasshour(rs.getInt("classhour"));
                su.setGradeid(rs.getLong("gradeid"));

                return su;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return null;
    }
    //增加
    public int add(Subject s){
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");
            String sql="insert into `s_subject`(`subjectname`,`classhour`,`gradeid`)values(?,?,?)";
            System.out.println(sql);
            ps=connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setString(1,s.getSubjectname());
            ps.setInt(2,s.getClasshour());
            ps.setLong(3,s.getGradeid());
            zhi=ps.executeUpdate();
            System.out.printf("%d行被修改了",zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(connection);
        }
        return zhi;
    }
    //删除
    public int del( int id){
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");
            String sql="delete from s_subject where id=?";
            System.out.println(sql);
            ps=connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setInt(1,id);
            zhi=ps.executeUpdate();
            System.out.printf("%d行被修改了",zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(connection);
        }
        return zhi;
    }
    //修改
//    public int update(Subject s){
//        Connection connection = DBUtil.getConnection();
//        PreparedStatement ps=null;
//        try {
//            System.out.println(connection.getClass().getName());
//            System.out.println("连接已经建立");
//
//            String sql="update s_subject set subjectname=?,classhour=?,gradeid=? where id=?";
//            System.out.println(sql);
//            ps=connection.prepareStatement(sql);
//            //!!给占位符赋值
//            ps.setString(1,s.getSubjectname());
//            ps.setInt(2,s.getClasshour());
//            ps.setLong(3,s.getGradeid());
//            ps.setInt(4,s.getId());
//            int zhi=ps.executeUpdate();
//            System.out.printf("%d行被修改了",zhi);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            DBUtil.close(ps);
//            DBUtil.close(connection);
//        }
//    }
}
